Option Strict On

Imports Excel = Microsoft.Office.Interop.Excel
'Imports Office = Microsoft.Office.Core
Imports System.Text.RegularExpressions

Public Class Tables

    Private Const defaultSearchSymbol As Char = "%"c
    Private Const defaultSearchPattern As String = defaultSearchSymbol & defaultSearchSymbol

    Public Shared Function FindTables(ByVal rangeToSearch As Excel.Range, ByVal initData As DataSet) As IList
        Dim rngFound As Excel.Range
        Dim findList As ArrayList = New ArrayList

        Dim rngFoundFirst As Excel.Range

        rngFound = rangeToSearch.Find( _
            defaultSearchPattern, , _
            Excel.XlFindLookIn.xlFormulas, Excel.XlLookAt.xlPart, _
            Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, False)

        While Not rngFound Is Nothing
            If rngFoundFirst Is Nothing Then
                rngFoundFirst = rngFound
            ElseIf rngFound.Address = rngFoundFirst.Address Then
                Exit While
            End If
            Try
                Dim o As Object = rngFound.PivotField
            Catch ex As Exception
                Dim table As ITemplateTable = CreateTemplateTable(rngFound, initData)
                If (Not table Is Nothing) Then
                    findList.Add(table)
                End If
            End Try
            rngFound = rangeToSearch.FindNext(rngFound)
        End While
        Return findList
    End Function

    Private Shared Function CreateTemplateTable(ByRef location As Excel.Range, ByRef initData As DataSet) As ITemplateTable
        Dim table As ITemplateTable
        Dim names As String() = location.Value.ToString().TrimStart(defaultSearchSymbol).Split("."c)
        If (IsTable(location)) Then
            If (Not names(1).TrimEnd(" "c) = String.Empty) Then
                table = New TableColumn(names(0), names(1), initData, location)
            Else
                table = New TemplateTable(names(0), initData, location)
            End If
        End If
        Return table
    End Function

    Private Shared Function IsTable(ByVal location As Excel.Range) As Boolean
        Return Regex.Match(location.Value.ToString, "%%\w+\.\w*").Success
    End Function

End Class
